USE studb
GO

CREATE VIEW scview
AS
SELECT student.sno, sname, sex, birthday, department, classno, score, 
course.cno, cname, lecture, semester, credit FROM student
JOIN student_course ON student.sno=student_course.sno
JOIN course ON course.cno=student_course.cno;

CREATE VIEW sctview
AS
SELECT student.sno, sname, student.sex student_sex, birthday student_birthday,
		 student.department student_department, classno, score, 
		 course.cno, cname, lecture, semester, credit,
		 teacher.tno, tname, teacher.sex teacher_sex, email, teacher.department teacher_department
		 FROM student
JOIN student_course ON student.sno=student_course.sno
JOIN course ON course.cno=student_course.cno
JOIN teacher_course ON course.cno=teacher_course.cno
JOIN teacher ON teacher.tno=teacher_course.tno;

CREATE VIEW ctview
AS
SELECT course.cno, cname, lecture, semester, credit, 
		teacher.tno, tname, sex, email, department
FROM course 
JOIN teacher_course ON course.cno=teacher_course.cno
JOIN teacher ON teacher.tno=teacher_course.tno;